<?php

namespace Core\Data\SQL;

class ConnectionEx extends Connection {

    function __construct ( ServerInformation $ServerInformation, $boolPersistent = false ) {
        parent::__construct( $ServerInformation, $boolPersistent );
    }

    /**
     * Gets the databases on the server.
     *
     * @return array
     */
    public function getDatabases () {

        $strQuery = "SHOW DATABASES";
        $arrResult = $this->fetch( $strQuery );

        $arrReturn = array();

        foreach ( $arrResult as $arrDatabase ) {
            $arrReturn[] = $arrDatabase['Database'];
        }

        return $arrReturn;

    }

    /**
     * Gets the tables in the current database.
     *
     * @return array|null An array of the table names. Null if there are no tables.
     */
    public function getTables () {

        $strQuery = "SHOW TABLES";
        $arrResult = $this->fetch( $strQuery );

        $arrReturn = array();

        // NOTE: If there is no tables, $arrResult will not be an array, hence the (array).
        foreach ( (array) $arrResult as $arrTable ) {
            $arrReturn[] = $arrTable['Tables_in_' . $this->getCurrentDatabase()];
        }

        return ( $arrResult ) ? $arrReturn : null;

    }

    /**
     * Gets the fields in a table in the current database.
     *
     * @param string $strTableName
     * @return array An array containing one instance of FieldInformation for each field.
     */
    public function getFields ( $strTableName ) {

        $strQuery = "SHOW COLUMNS IN " . $strTableName;
        $arrResult = $this->fetch( $strQuery );

        $arrReturn = array();

        foreach ( $arrResult as $arrField ) {

            $objFieldInformation = new FieldInformation ();
            $objFieldInformation->Name = $arrField['Field'];

            $strPattern = "/([a-z]+)(\(([\d]+)\))?/";
            preg_match( $strPattern, $arrField['Type'], $arrMatches );

            $objFieldInformation->Type = $arrMatches[1];

            if ( isset( $arrMatches[3] ) ) {
                $objFieldInformation->Size = (integer) $arrMatches[3];
            }
            
            $objFieldInformation->Null = ( $arrField['Null'] == "YES" ) ? true : false;
            $objFieldInformation->AutoIncrement = ( $arrField['Extra'] == "auto_increment" ) ? true : false;
            $objFieldInformation->PrimaryKey = ( $arrField['Key'] == "PRI" ) ? true : false;

            $arrReturn[] = $objFieldInformation;
        }

        return $arrReturn;

    }

    public function getFieldInformation ( $strFieldName, $strTableName ) {

        foreach ( $this->getFields( $strTableName ) as $FieldInformation ) {
            if ( $FieldInformation->Name == (string) $strFieldName ) {
                return $FieldInformation;
            }
        }

    }

    /**
     * Checks if a tables exists.
     *
     * @param string $strTable
     * @return boolean
     */
    public function isTable ( $strTableName ) {
        return in_array( (string) $strTableName, (array) $this->getTables() );
    }

    /**
     * Checks if a field exists.
     *
     * @param string $strField
     * @param string $strTable
     * @return boolean
     */
    public function isField ( $strFieldName, $strTableName ) {
        foreach ( $this->getFields( $strTableName ) as $objFieldInformation ) {
            if ( $objFieldInformation->Name == (string) $strFieldName ) {
                return true;
            }
        }
        return false;
    }

    /**
     * Unsets the primary key in a table, if set.
     *
     * @param string $strTableName
     * @return boolean|string
     */
    public function unsetPrimaryKey ( $strTableName ) {

        // Check if there is a primary key in the table.
        $strPrimaryKey = $this->getPrimaryKey( $strTableName );
        if ( $strPrimaryKey ) {

            // Check if the primary key is auto incrementing.
            $objFieldInformation = $this->getFieldInformation( $strPrimaryKey, $strTableName );
            if ( $objFieldInformation->AutoIncrement ) {
                // We have to remove auto increment before we can remove the primary key.

                $strQuery = "ALTER TABLE `" . $strTableName . "` CHANGE `" . $strPrimaryKey . "` `" . $strPrimaryKey . "` " . $objFieldInformation->Type;
                $strQuery .= ( $objFieldInformation->Size ) ? "(" . $objFieldInformation->Size . ")" : "";
                $strQuery .= ( $objFieldInformation->Null ) ? " NULL" : " NOT NULL";

                $this->query( $strQuery );

            }

            // Remove the primary key.
            $strQuery = "ALTER TABLE `" . $strTableName . "` DROP PRIMARY KEY";
            $this->query( $strQuery );
            return $strPrimaryKey;
        }

        // There is no primary key in the table.
        return false;

    }

    /**
     * Gets the field that is set as primary key, if any.
     *
     * @param string $strTableName
     * @return boolean|string
     */
    public function getPrimaryKey ( $strTableName ) {

        foreach ( $this->getFields( $strTableName ) as $objFieldInformation ) {

            if ( $objFieldInformation->PrimaryKey ) {
                return $objFieldInformation->Name;
            }

        }

        return false;

    }

    public function renameField ( $strCurrentFieldName, $strNewFieldName, $strTableName ) {

        $objFieldInformation = $this->getFieldInformation( $strCurrentFieldName, $strTableName );

        $strQuery = "ALTER TABLE `" . $strTableName . "` CHANGE `" . $strCurrentFieldName . "` `" . $strNewFieldName . "` " . $objFieldInformation->Type;
        $strQuery .= ( $objFieldInformation->Size ) ? "(" . $objFieldInformation->Size . ")" : "";
        $strQuery .= ( $objFieldInformation->Null ) ? " NULL" : " NOT NULL";
        $strQuery .= ( $objFieldInformation->AutoIncrement ) ? " AUTO_INCREMENT" : "";
        
        $this->query( $strQuery );

    }

    public function renameTable ( $strCurrentTableName, $strNewTableName ) {

        $strQuery = "RENAME TABLE `" . $strCurrentTableName . "` TO `" . $strNewTableName . "`";
        
        $this->query( $strQuery );

    }

    /**
     * Creates a field to a table.
     *
     * @param FieldInformation $objFieldInformation
     * @param string $strTableName
     */
    public function createField ( FieldInformation $objFieldInformation, $strTableName ) {

        $strQuery = "ALTER TABLE `" . $strTableName . "` ADD `" . $objFieldInformation->Name . "` " . $objFieldInformation->Type;
        $strQuery .= ( $objFieldInformation->Size ) ? "(" . $objFieldInformation->Size . ")" : "";
        $strQuery .= ( $objFieldInformation->Null ) ? " NULL" : " NOT NULL";

        if ( $objFieldInformation->AutoIncrement ) {

            $strQuery .= " AUTO_INCREMENT";

            // Unset the existing auto incrementing field, if one is set.
            $this->unsetAutoIncrement( $strTableName );

        }

        if ( $objFieldInformation->PrimaryKey ) {

            $strQuery .= " PRIMARY KEY";

            // Unset the existing primary key, if one is set.
            $this->unsetPrimaryKey( $strTableName );

        }

        $this->query( $strQuery );

    }

    /** 
     * Creates a table to the database.
     *
     * Takes any number of instances of FieldInformation as parameters.
     *
     * @param string $strTableName
     * @param FieldInformation
     */
    public function createTable ( $strTableName ) {

        

    }

    /**
     * Gets the field that auto increments.
     *
     * @param string $strTableName
     * @return boolean|string
     */
    public function getAutoIncrementingField ( $strTableName ) {

        foreach ( $this->getFields( $strTableName ) as $objFieldInformation ) {

            if ( $objFieldInformation->AutoIncrement ) {
                return $objFieldInformation->Name;
            }

        }

        return false;

    }

    /**
     * Unsets auto increment from the field that does, if any.
     *
     * @param string $strTableName
     * @return boolean|string
     */
    public function unsetAutoIncrement ( $strTableName ) {

        // Check if there is a auto incrementing field in the table.
        $strAutoIncrementingField = $this->getAutoIncrementingField( $strTableName );
        if ( $strAutoIncrementingField ) {

            $objFieldInformation = $this->getFieldInformation( $strAutoIncrementingField, $strTableName );

            $strQuery = "ALTER TABLE `$strTableName` CHANGE `$objFieldInformation->Name` `$objFieldInformation->Name` $objFieldInformation->Type";
            $strQuery .= ( $objFieldInformation->Size ) ? "($objFieldInformation->Size)" : "";
            $strQuery .= ( $objFieldInformation->Null ) ? " NULL" : " NOT NULL";

            $this->query( $strQuery );

            return $strAutoIncrementingField;

        }

        // There is no auto incrementing field in the table.
        return false;

    }

    /**
     * Wipes the current database.
     *
     * Removes all the tables and their data without deleting the database itself.
     */
    public function wipe () {

        if ( $this->getTables() ) {

            $strQuery = "DROP TABLES ";

            foreach ( $this->getTables() as $strTable ) {
                $strQuery .= "`" . $strTable . "`, ";
            }

            $strQuery = substr( $strQuery, 0, -2 );

            $this->query( $strQuery );

        }

    }

    public function export () {

    }

}

?>
